package com.dc.free.db.service;

import java.util.List;

import com.dc.free.db.model.CGoods;
import com.jfinal.plugin.activerecord.Page;

public class CGoodsService {
	
	static CGoods dao = CGoods.dao;

	public static Page<CGoods> list(String actionId, int pageNumber, int pageSize) {
		return dao.paginate(pageNumber, pageSize, "select *", "from c_goods where action_id=?", actionId);
	}
	
	public static Page<CGoods> list(String actionId, String name, int pageNumber, int pageSize) {
		return dao.paginate(pageNumber, pageSize, "select *", "from c_goods where name like ? action_id=?", "%"+name+"%", actionId);
	}
	
	public static Page<CGoods> listWithBidding(String actionId, int pageNumber, int pageSize) {
		return dao.paginate(pageNumber, pageSize, 
				"select g.*, t.no, t.price max_price ", 
				"from c_goods g LEFT JOIN " +
				"( " +
				"select * from (select b.*,u.no from c_bidding  b left join c_user u on b.user_id = u.id where b.action_id = ? and u.action_id=? " +
				"order by price desc) as a group by a.goods_id " +
				") t " +
				"on g.id=t.goods_id " +
				"where g.action_id=? ", 
				actionId, actionId, actionId);
	}
	
	public static Page<CGoods> listWithBidding(String actionId, String name, int pageNumber, int pageSize) {
		return dao.paginate(pageNumber, pageSize, 
				"select g.*, t.no, t.price max_price ", 
				"from c_goods g LEFT JOIN " +
				"( " +
				"select * from (select b.*,u.no from c_bidding  b left join c_user u on b.user_id = u.id where b.action_id = ? and u.action_id=? " +
				"order by price desc) as a group by a.goods_id " +
				") t " +
				"on g.id=t.goods_id " +
				"where g.name like ? and g.action_id = ?", 
				actionId, actionId, "%"+name+"%", actionId);
	}

	public static CGoods getGoods(String id) {
		return dao.findById(id);
	}
	
	public static CGoods getGoodsWithBiddingMax(String actionId, String goodsId) {
		return dao.findFirst("select g.*, t.user_id, t.max_price " +
				"from c_goods g left join " +
				"(select b.*, max(price) max_price from c_bidding b where b.action_id= ? and b.goods_id = ?) t " +
				"on g.id=t.goods_id " +
				"where g.action_id = ? and g.id = ?",
				actionId, goodsId, actionId, goodsId);
	}

	public static List<CGoods> names(String name, String actionId) {
		return dao.find("select name from c_goods where name like ? and action_id = ?", "%"+name+"%", actionId);
	}

}
